---
title: "SQL-028: Fee auditor policy list"
summary: Retrieve the list of active policies accessible to a fee auditor.
status: draft
owner: SAIF Corporation
last_reviewed: 2026-08-21
library: SAIF.Business.View.Policy
reference_type: embedded
---

# SQL-028 Fee auditor policy list

This SQL retrieves the policy list for a fee auditor by joining active audit assignments to policy names so the UI can present accessible policies.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-028 |
| **Operation type** | Read |
| **Execution method** | Inline |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsPolicySecurity` |
| **Criteria property** | `GetAuditorPolicyListSQL` |
| **Lifecycle method** | `GetPolicyListForFeeAuditor` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:VendorNum` | Input | Decimal | N/A | Auditor vendor number used to find accessible policies. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PA_AUDIT](../database-tables.md#pa_audit) | SELECT / JOIN | `policy_num`, `pa_audit_seq_num`, `pa_revises_seq_num`, `pa_auditor_id`, `cancel_date`, `completed_date |
| [PA_AUDITOR](../database-tables.md#pa_auditor) | JOIN | `pa_auditor_id`, `vendor_num |
| [POLICY](../database-tables.md#policy) | JOIN | `policy_num`, `policy_name |

## Sample SQL

```sql
SELECT
  pa.POLICY_NUM,
  pol.POLICY_NAME
FROM pa_audit pa
LEFT OUTER JOIN pa_audit pa2
  ON pa2.pa_revises_seq_num = pa.pa_audit_seq_num
JOIN pa_auditor a
  ON a.PA_AUDITOR_ID = pa.pa_auditor_id
JOIN policy pol
  ON pol.POLICY_NUM = pa.POLICY_NUM
WHERE a.VENDOR_NUM = :VendorNum
  AND NVL(pa.cancel_date, SYSDATE + 60) >= SYSDATE + 60
  AND NVL(pa.completed_date, SYSDATE + 60) >= SYSDATE + 60
ORDER BY 1 ASC
```

## Operations and processes
- [Retrieve accessible policy list](../operations-processes.md)

## Business rules
- [BR-VP-005: Access granted per user-access-level matrix](../business-rules.md#br-vp-005-access-granted-per-user-access-level-matrix)
- [BR-VP-006: Auditor 60-day access window](../business-rules.md#br-vp-006-auditor-60-day-access-window)

## Sources
- `SAIF.Business.View.Policy/clsPolicySecurity.vb`